package com.mes.handler;

import java.io.File;
import java.util.*;

import com.basis.utils.HibernateDao;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;

/**
 *
 * @author wcyong
 *
 * @date   2013-6-21
 */
public class SCBomImporter {


    //尺码开始的下标
    private int sizeIndex = 0;
    //存储list集合
    private List<Map<String,String>> records;
    //将record记录成Map；
    private Map<String,String> record;
    //记录表头信息
    private Map<String,String> secRecord =  new HashMap();
    //
    private String xtName;
    //记录已经插入的parts，防止重复插入
    private Map<String,String> aParts;



    public static void main(String [] args) {
        SCBomImporter sss = new SCBomImporter();
        sss.readExcelToObj("F:\\项目文档\\08.中电项目资料\\018.客户提供基础数据\\k1905wm002双驰MES主数据收集模板(2)(3) - 副本 - 副本.xlsx");
    }

    /**
     * 读取excel数据
     * @param path
     */
    private void readExcelToObj(String path) {

        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(new File(path));

            readExcel(wb, 0, 0, 0);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取excel文件
     * @param wb
     * @param sheetIndex sheet页下标：从0开始
     * @param startReadLine 开始读取的行:从0开始
     * @param tailLine 去除最后读取的行
     */
    private void readExcel(Workbook wb,int sheetIndex, int startReadLine, int tailLine) throws JSONException {
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        records = new ArrayList<>();
        for(int i=startReadLine; i<sheet.getLastRowNum()-tailLine+1; i++) {
            row = sheet.getRow(i);
            record = new HashMap();
            //记录每一个Cell的位置
            int j = 0;
            for(Cell c : row) {
                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                //判断是否具有合并单元格
                if(isMerge) {
                    if(j == 1){ aParts.put(getCellValue(c),"NONE");}
                    String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
                    //第一行获取型体数据
                    if(xtName == null) xtName = rs;
                    if(secRecord.size() != 0) record.put(secRecord.get(j+""),rs);
                    j++;
                    continue;
                }else {
                    if(j == 1){ aParts.put(getCellValue(c),"NONE");}
                    //第二行获取表头数据
                    if(row.getRowNum() == 1){
                        secRecord.put(j+"",getCellValue(c));
                        j++;
                        continue;
                    }
                    record.put(secRecord.get(j+""),getCellValue(c));
                    j++;
                }
            }
            records.add(record);
        }
        System.out.println(records.toArray());
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public String getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){

                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell) ;
                }
            }
        }

        return null ;
    }

    /**
     * 判断合并了行
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    private boolean isMergedRow(Sheet sheet,int row ,int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row == firstRow && row == lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    private boolean isMergedRegion(Sheet sheet,int row ,int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断sheet页中是否含有合并单元格
     * @param sheet
     * @return
     */
    private boolean hasMerged(Sheet sheet) {
        return sheet.getNumMergedRegions() > 0 ? true : false;
    }

    /**
     * 合并单元格
     * @param sheet
     * @param firstRow 开始行
     * @param lastRow 结束行
     * @param firstCol 开始列
     * @param lastCol 结束列
     */
    private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell){

        if(cell == null) return "";

        if(cell.getCellType() == Cell.CELL_TYPE_STRING){

            return cell.getStringCellValue();

        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){

            return String.valueOf(cell.getBooleanCellValue());

        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){

            return cell.getCellFormula() ;

        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

            return String.valueOf(cell.getNumericCellValue());

        }else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){

            return "NONE";

        }
        return "";
    }

    //插入数据库数据
    public JSONObject insertData(List<Map<String,String>> list){
        for(Map<String,String> map:list){


        }
        return null;
    }

    //插入型体
    public JSONObject insertXT(String name) throws JSONException {
        JSONObject result = new JSONObject();
        String sql = "select count(*) from m_product t where t.name = '" + name + "'";
        try{
            Long count = HibernateDao.getSqlCount(sql);
            if(count == 0){
                String insertSql = "insert into m_product (" +
                        "m_product_id," +
                        "name," +
                        //"emes_xuantou_id," +
                        //"product_spec," +
                        "c_uom_id," +
                        //"emes_material_id," +
                        "isactive," +
                        "discontinued," +
                        //"description," +
                        "ad_org_id," +
                        "ad_client_id," +
                        "created," +
                        "createdby," +
                        "updated," +
                        "updatedby) values(" +
                        "'"+ name +"'," +
                        "'"+ name +"型体'," +
                        //"'"+ name +"'" +
                        //"'"+ name +"'" +
                        "'F06B5BD964A14A7B9D1DAE7D06404429'," +
                        "'Y'," +
                        "'N'," +
                        //"''," +
                        "'33B4179374F74FD3AA430BF9CC0EAFDA',"+
                        "'0651A5660FFF4E3891091E44B2FF2F81'," +
                        "'LOCALTIMESTAMP'," +
                        "'09BD8B43D0AE4FE9BE879D012F9739BC',"+
                        "'LOCALTIMESTAMP'," +
                        "'09BD8B43D0AE4FE9BE879D012F9739BC'"+
                        ")";
            }
        }catch (Exception e){
            result.put("flag",false);
            result.put("msg","异常信息："+e.getMessage());
            return result;
        }
        result.put("flag",true);
        result.put("msg","成功");
        return result;
    }

    //插入型体BOM
    public void insertBOMItem(List<Map<String,String>> list,String bomId){
        String insertBOMItemID = "";
        String insertBOMItem = "";
        for (Map<String,String> map:list){
            if(!aParts.containsKey(map.get("部位名称"))){

                insertBOMItemID = UUID.randomUUID().toString().replace("-", "");
                String insertBom = "INSERT INTO \"public\".\"emes_pbom_item\"(" +
                        "emes_pbom_item_id," +
                        "emes_pbom_id," +
                        //"m_proudct_id," +
                        //"parent_id," +
                        "qty," +
                        "is_expan_item," +
                        "ad_client_id," +
                        "ad_org_id," +
                        "isactive," +
                        "created," +
                        "createdby," +
                        "updated," +
                        "updatedby," +
                        "emes_component_id," +
                        "stop_expan," +
                        "is_virtual_part," +
                        "c_uom_id," +
                        "level," +
                        "seqno," +
                        "emes_color_id," +
                        "name," +
                        "code," +
                        "id_diy) VALUES ('" +
                        insertBOMItemID+"', " +
                        "'"+bomId+"', " +
                        //"NULL, " +
                        //"'D2AA389FA2D44CD5934BD2CF8501C759'," +
                        " 1.000, " +
                        " 'N', " +
                        " '0651A5660FFF4E3891091E44B2FF2F81'," +
                        " '33B4179374F74FD3AA430BF9CC0EAFDA'," +
                        " 'Y', " +
                        " '2019-09-05 10:36:49.955', " +
                        " '09BD8B43D0AE4FE9BE879D012F9739BC', " +
                        " '2019-09-10 09:26:57.893', " +
                        " '09BD8B43D0AE4FE9BE879D012F9739BC', " +
                        " 'N'," +
                        " 'KXX'," +
                        " 'N'," +
                        " 'N'," +
                        " '巴斯夫'," +
                        " 'BSF');\n";
            }
        }
    }


    public JSONObject BomItemHandler(Map<String,String> map,String name){
        //keySet获取map集合key的集合  然后在遍历key即可
        String querySql = " select count(*) from emes_component t where t.name = '?'";
        String insertSql = " insert table emes_component (" +
                "emes_component_id," +
                "code," +
                "descrption," +
                "ad_client_id," +
                "ad_org_id," +
                "isactive," +
                "created," +
                "createdby," +
                "updated," +
                "updatedby," +
                "emes_process)" +
                "values(" +
                "''" +
                ")";
        String value;
        for(String key:map.keySet()){
            value = map.get(key);
            Long count = HibernateDao.getSqlCount(querySql.replace("?",value));

        }
        return null;
    }

    //public JSONObject

}